package com.jfinal.plugin.activerecord;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.cache.ICache;

public class NamedParameterDbPro {
	private final Config config;
	private static final Map<String, NamedParameterDbPro> map = new HashMap<String, NamedParameterDbPro>();
	static final Map<String, Object> EMPTY_PARA_MAP = new HashMap<String, Object>(0);

	public NamedParameterDbPro() {
		if (DbKit.config == null) throw new RuntimeException("The main config is null, initialize ActiveRecordPlugin first");
		this.config = DbKit.config;
	}

	public NamedParameterDbPro(String configName) {
		this.config = DbKit.getConfig(configName);
		if (this.config == null) throw new IllegalArgumentException("Config not found by configName: " + configName);
	}

	public static NamedParameterDbPro use() {
		return use(DbKit.config.name);
	}

	public static NamedParameterDbPro use(String configName) {
		NamedParameterDbPro result = map.get(configName);
		if (result == null) {
			result = new NamedParameterDbPro(configName);
			map.put(configName, result);
		}
		return result;
	}

	public <T> List<T> query(Config config, Connection conn, String namedSql, Map<String, Object> paraMap) throws SQLException {
		ParsedSql ps = ParsedSql.parseNamedSqlStatement(namedSql, paraMap);
		Object[] paras = ps.getParas();
		String sql = ps.getSql();
		return Db.query(config, conn, sql, paras);
	}

	/**
	 * @see #query(String, String, Map<String,Object>)
	 */
	public <T> List<T> query(String namedSql, Map<String, Object> paraMap) {
		Connection conn = null;
		try {
			conn = config.getConnection();
			return query(config, conn, namedSql, paraMap);
		} catch (Exception e) {
			throw new ActiveRecordException(e);
		} finally {
			config.close(conn);
		}
	}

	/**
	 * Execute sql query and return the first result. I recommend add "limit 1" in your sql.
	 * @param sql an SQL statement that may contain one or more '?' IN parameter placeholders
	 * @param paras the parameters of sql
	 * @return Map<String, Object> if your sql has select more than one column,
	 * 			and it return Object if your sql has select only one column.
	 */
	public <T> T queryFirst(String namedSql, Map<String, Object> paraMap) {
		List<T> result = query(namedSql, paraMap);
		return (result.size() > 0 ? result.get(0) : null);
	}

	// 26 queryXxx method below -----------------------------------------------
	/**
	 * Execute sql query just return one column.
	 * @param <T> the type of the column that in your sql's select statement
	 * @param sql an SQL statement that may contain one or more '?' IN parameter placeholders
	 * @param paras the parameters of sql
	 * @return List<T>
	 */
	public <T> T queryColumn(String namedSql, Map<String, Object> paraMap) {
		List<T> result = query(namedSql, paraMap);
		if (result.size() > 0) {
			T temp = result.get(0);
			if (temp instanceof Object[]) throw new ActiveRecordException("Only ONE COLUMN can be queried.");
			return temp;
		}
		return null;
	}

	public String queryStr(String sql, Map<String, Object> paraMap) {
		return (String) queryColumn(sql, paraMap);
	}

	public Integer queryInt(String sql, Map<String, Object> paraMap) {
		return (Integer) queryColumn(sql, paraMap);
	}

	public Long queryLong(String sql, Map<String, Object> paraMap) {
		return (Long) queryColumn(sql, paraMap);
	}

	public Double queryDouble(String sql, Map<String, Object> paraMap) {
		return (Double) queryColumn(sql, paraMap);
	}

	public Float queryFloat(String sql, Map<String, Object> paraMap) {
		return (Float) queryColumn(sql, paraMap);
	}

	public java.math.BigDecimal queryBigDecimal(String sql, Map<String, Object> paraMap) {
		return (java.math.BigDecimal) queryColumn(sql, paraMap);
	}

	public byte[] queryBytes(String sql, Map<String, Object> paraMap) {
		return (byte[]) queryColumn(sql, paraMap);
	}

	public java.util.Date queryDate(String sql, Map<String, Object> paraMap) {
		return (java.util.Date) queryColumn(sql, paraMap);
	}

	public java.sql.Time queryTime(String sql, Map<String, Object> paraMap) {
		return (java.sql.Time) queryColumn(sql, paraMap);
	}

	public java.sql.Timestamp queryTimestamp(String sql, Map<String, Object> paraMap) {
		return (java.sql.Timestamp) queryColumn(sql, paraMap);
	}

	public Boolean queryBoolean(String sql, Map<String, Object> paraMap) {
		return (Boolean) queryColumn(sql, paraMap);
	}

	public Number queryNumber(String sql, Map<String, Object> paraMap) {
		return (Number) queryColumn(sql, paraMap);
	}

	// 26 queryXxx method under -----------------------------------------------

	/**
	 * Execute sql update
	 */
	public int update(Config config, Connection conn, String namedSql, Map<String, Object> paraMap) throws SQLException {
		ParsedSql ps = ParsedSql.parseNamedSqlStatement(namedSql, paraMap);
		Object[] paras = ps.getParas();
		String sql = ps.getSql();
		return Db.update(config, conn, sql, paras);
	}

	/**
	 * Execute update, insert or delete sql statement.
	 * @param sql an SQL statement that may contain one or more '?' IN parameter placeholders
	 * @param paras the parameters of sql
	 * @return either the row count for <code>INSERT</code>, <code>UPDATE</code>,
	 *         or <code>DELETE</code> statements, or 0 for SQL statements 
	 *         that return nothing
	 */
	public int update(String sql, Map<String, Object> paraMap) {
		Connection conn = null;
		try {
			conn = config.getConnection();
			return update(config, conn, sql, paraMap);
		} catch (Exception e) {
			throw new ActiveRecordException(e);
		} finally {
			config.close(conn);
		}
	}

	public List<Record> find(Config config, Connection conn, String namedSql, Map<String, Object> paraMap) throws SQLException {
		ParsedSql ps = ParsedSql.parseNamedSqlStatement(namedSql, paraMap);
		Object[] paras = ps.getParas();
		String sql = ps.getSql();
		return Db.find(config, conn, sql, paras);
	}

	/**
	 * @see #find(String, String, Map<String, Object>)
	 */
	public List<Record> find(String sql, Map<String, Object> paraMap) {
		Connection conn = null;
		try {
			conn = config.getConnection();
			return find(config, conn, sql, paraMap);
		} catch (Exception e) {
			throw new ActiveRecordException(e);
		} finally {
			config.close(conn);
		}
	}

	/**
	 * Find first record. I recommend add "limit 1" in your sql.
	 * @param sql an SQL statement that may contain one or more '?' IN parameter placeholders
	 * @param paras the parameters of sql
	 * @return the Record object
	 */
	public Record findFirst(String sql, Map<String, Object> paraMap) {
		List<Record> result = find(sql, paraMap);
		return result.size() > 0 ? result.get(0) : null;
	}

	public Page<Record> paginate(Config config, Connection conn, int pageNumber, int pageSize, String select,
			String sqlExceptSelect, Map<String, Object> paraMap) throws SQLException {
		if (pageNumber < 1 || pageSize < 1) throw new ActiveRecordException("pageNumber and pageSize must be more than 0");

		if (config.dialect.isTakeOverDbPaginate())
			return config.dialect.takeOverDbPaginate(conn, pageNumber, pageSize, select, sqlExceptSelect, paraMap);

		long totalRow = 0;
		int totalPage = 0;
		List<?> result = query(config, conn, "select count(*) " + DbKit.replaceFormatSqlOrderBy(sqlExceptSelect), paraMap);
		int size = result.size();
		if (size == 1) totalRow = ((Number) result.get(0)).longValue();
		else if (size > 1) totalRow = result.size();
		else return new Page<Record>(new ArrayList<Record>(0), pageNumber, pageSize, 0, 0);

		totalPage = (int) (totalRow / pageSize);
		if (totalRow % pageSize != 0) {
			totalPage++;
		}

		// --------
		StringBuilder sql = new StringBuilder();
		config.dialect.forPaginate(sql, pageNumber, pageSize, select, sqlExceptSelect);
		List<Record> list = find(config, conn, sql.toString(), paraMap);
		return new Page<Record>(list, pageNumber, pageSize, totalPage, (int) totalRow);
	}

	/**
	 * @see #paginate(String, int, int, String, String, Map<String, Object>)
	 */
	public Page<Record> paginate(int pageNumber, int pageSize, String select, String sqlExceptSelect,
			Map<String, Object> paraMap) {
		Connection conn = null;
		try {
			conn = config.getConnection();
			return paginate(config, conn, pageNumber, pageSize, select, sqlExceptSelect, paraMap);
		} catch (Exception e) {
			throw new ActiveRecordException(e);
		} finally {
			config.close(conn);
		}
	}

	/**
	 * @see #paginate(String, int, int, String, String)
	 */
	public Page<Record> paginate(int pageNumber, int pageSize, String select, String sqlExceptSelect) {
		return paginate(pageNumber, pageSize, select, sqlExceptSelect, EMPTY_PARA_MAP);
	}

	/**
	 * Find Record by cache.
	 * @see #find(String, Object...)
	 * @param cacheName the cache name
	 * @param key the key used to get date from cache
	 * @return the list of Record
	 */
	public List<Record> findByCache(String cacheName, Object key, String sql, Map<String, Object> paraMap) {
		ICache cache = config.getCache();
		List<Record> result = cache.get(cacheName, key);
		if (result == null) {
			result = find(sql, paraMap);
			cache.put(cacheName, key, result);
		}
		return result;
	}

	/**
	 * @see #findByCache(String, Object, String, Object...)
	 */
	public List<Record> findByCache(String cacheName, Object key, String sql) {
		return findByCache(cacheName, key, sql, EMPTY_PARA_MAP);
	}

	/**
	 * Paginate by cache.
	 * @see #paginate(int, int, String, String, Object...)
	 * @return Page
	 */
	public Page<Record> paginateByCache(String cacheName, Object key, int pageNumber, int pageSize, String select,
			String sqlExceptSelect, Map<String, Object> paraMap) {
		ICache cache = config.getCache();
		Page<Record> result = cache.get(cacheName, key);
		if (result == null) {
			result = paginate(pageNumber, pageSize, select, sqlExceptSelect, paraMap);
			cache.put(cacheName, key, result);
		}
		return result;
	}

	/**
	 * @see #paginateByCache(String, Object, int, int, String, String, Object...)
	 */
	public Page<Record> paginateByCache(String cacheName, Object key, int pageNumber, int pageSize, String select,
			String sqlExceptSelect) {
		return paginateByCache(cacheName, key, pageNumber, pageSize, select, sqlExceptSelect, EMPTY_PARA_MAP);
	}
}
